<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Paypal_m extends CI_Model {
	function __construct()
    {
        parent::__construct();
    }


public function SelectApp($order_sn='*',$shopid='*',$platfrom='*',$where='order_sn',$order='0',$orderd="desc",$timetype,$starttime,$endtime){
  //获取当前用户id，获取所属部门，判断是否属于财务部
  //第一步判断当前用户是否具有审批权 224
  //第二步获取所管理的店铺名单 
  $userid=$_COOKIE['userid'];
  //获取当前用户的所有权限,如果有权限的话，查看所属平台的内容，如果没有权限，这查看所管理店铺的权限，权限为all的不限制
  $powersql="select * from `d_user` where `id` = '{$userid}';";
  $power=$this->db->query($powersql)->result_array();
  $addsql='';
  $platarr=array('1'=>'eBay','2'=>'magento','4'=>'Aliexpress','9'=>'other');
  foreach ($power as $value) {
    //判断是否属于财务部的。属于财务部门的查看飞ebay，和magento的
     if($value['classid'] == 11){
      $addsql.=" and (pr.platfromname != 'eBay' and pr.platfromname != 'magento') ";
      break;
     }

    $is_break=1;
    if($value['power'] != 'all'){
      $powerarr=explode(',', $value['power']);
      //224权限管理magento 254权限管理ebay ，255权限管理速卖通
      $addsql.=" and (";
      if (in_array('224', $powerarr)) {
        $addsql.="pr.platfromname ='magento' or pr.platfromname = 'shopify' or";
        $is_break=0;
      }
      
      if (in_array('254', $powerarr)) {
        $addsql.="pr.platfromname ='eBay' or ";
        $is_break=0;
      }
      
      if(in_array('255', $powerarr)){
        $addsql.="pr.platfromname ='Aliexpress' or ";
        $is_break=0;
      }
      if(empty($value['plat'])) 
        $plat='9';
      else
        $plat=$value['plat'];
      $addsql.=" pr.platfromname ='{$platarr[$plat]}' ) ";
      if($is_break==0) break;
   
        //这个是没有权限的，默认看到自己所管理店铺的
        //cookie获取店铺名称
        $shoparr=unserialize( $_COOKIE['shopname']);
        $addsql.=" and ( ";
        foreach ($shoparr as $value) {
           $addsql.= "pr.shopname = '{$value}' or ";
        }
        $addsql.="pr.shopname != '')";
     
    }
  }

  if($page==0) $page=1;
  $page=$page*20-20;
  $jc='ol';
  if($where=="order_sn") $jc='pr';
//关于时间
  $strtime='';
  if($timetype=='refund_time'){
      if(!empty($starttime)){
        $starttime=strtotime($starttime);
      $strtime.=" and pr.{$timetype} >= '{$starttime}'";
      }
      if(!empty($endtime)){
      $endtime=strtotime($endtime)+3600*24;
      $strtime.=" and pr.{$timetype} <= '{$endtime}'";
      }

  }else{
      if(!empty($starttime)){
        $starttime=date('Y-m-d H:i:s',strtotime($starttime));
      $strtime.=" and pr.{$timetype} >= '{$starttime}'";
      }
      if(!empty($endtime)){
       $endtime=date('Y-m-d H:i:s',strtotime($endtime)+3600*24);
      $strtime.=" and pr.{$timetype} <= '{$endtime}'";
      }
  }


//优先级问题 订单号大于店铺id大于平台id
	$sql='select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where pr.order_sn=ol.order_sn '.$strtime.$addsql.' order by pr.pay_id '.$orderd;
  if($order!=0){
    $sql='select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where pr.order_sn=ol.order_sn and pr.refund_status="'.$order.'" '.$strtime.$addsql.' order by pr.pay_id '.$orderd;
    if($order==5){
      $sql='select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where pr.order_sn=ol.order_sn and pr.is_inpaypal="1" '.$strtime.$addsql.' order by pr.pay_id '.$orderd;
    }
  }
     if ($platfrom!='*') {
      $platarr=$this->platfromarr($platfrom);
      $shopql= implode('" or pr.shop_id="', $platarr);
     $sql='select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where (pr.shop_id="'.$shopql.'") and pr.order_sn=ol.order_sn '.$strtime.$addsql.' order by pr.pay_id '.$orderd;
     if($order!=0){
      $sql='select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where (pr.shop_id="'.$shopql.'") and pr.order_sn=ol.order_sn  and pr.refund_status="'.$order.'" '.$strtime.$addsql.' order by pr.pay_id '.$orderd;
      if($order==5){
      $sql='select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where (pr.shop_id="'.$shopql.'") and pr.order_sn=ol.order_sn  and pr.is_inpaypal="1" '.$strtime.$addsql.' order by pr.pay_id '.$orderd;
      }
     }
     }
     if ($shopid!='*') {
     	$sql="select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where pr.shop_id='{$shopid}' and pr.order_sn=ol.order_sn {$strtime} {$addsql} order by pr.pay_id {$orderd}";
      if($order!=0){
        $sql="select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where pr.shop_id='{$shopid}' and pr.order_sn=ol.order_sn and pr.refund_status='{$order}' {$strtime} {$addsql} order by pr.pay_id {$orderd} ";
        if($order==5){
         $sql="select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where pr.shop_id='{$shopid}' and pr.order_sn=ol.order_sn and pr.is_inpaypal='1' {$strtime} {$addsql} order by pr.pay_id {$orderd} "; 
        }
      }
     }
     if ($order_sn!='*') {
     	$sql="select pr.*,ol.customerid,ol.tradeid from db_paypal_refund as pr , db_order_list as ol where {$jc}.{$where}='{$order_sn}' and pr.order_sn=ol.order_sn {$strtime} {$addsql} order by pr.pay_id {$orderd}";
     }
return $sql;
}

public function selectarr($sql,$page){
  $start=20*$page-20;
  $sql=$sql." LIMIT {$start},20;";
        $arr=$this->db->query($sql)->result_array();
        $i=0;
        foreach ($arr as $value) {
            
           $arr[$i]['refund_time']= date('Y-m-d H:i:s',$value['refund_time']);
           $i++;
        }
        
     return $arr;
}

public function SelectNum($sql){
   $res=$this->db->query($sql)->result_array();
  return  $res;
}

public function tormb($currency){
  $sql="select * from d_moneyrate where name='{$currency}';";
  $res=$this->db->query($sql)->result_array();
  return $res[0]['rate'];
}

public function platfromarr($platfrom){

$arr= $this->db->query('select id from d_shop where platfrom="'.$platfrom.'"')->result_array();
$rearr=array();
foreach ($arr as $value) {
	$rearr[]=$value['id'];
}
return $rearr;
}


public function SelectShop(){

	return $this->db->query('select * from d_shop')->result_array();
}

public function SelectPlatform(){
	return $this->db->query('select * from db_platform')->result_array();
}

public function RefundToinfo($pay_id){

$info=$this->db->query('select * from db_paypal_refund where pay_id="'.$pay_id.'";')->result_array();


foreach ($info as $value) {
	// $infoarr[]=$value['order_sn'];
	// $infoarr[]=$value['refund_time'];
	// $infoarr[]=$value['refund_money'];
	// $infoarr[]=$value['refund_reason'];
	// $infoarr[]=$value['shopname'];
	// $infoarr[]=$value['platfromname'];
	// $infoarr[]=$value['shopoper'];
	// $infoarr[]=$value['goods_originask'];
	// $infoarr[]=$value['moneytype'];
	// $infoarr[]=$value['ordertime'];
    $refundtime=date('Y-m-d H:i:s',$value['refund_time']);
  $infoarr = array('code'=>1,'msg'=>'获取成功!','pay_id'=>$value['pay_id'],'order_sn'=>$value['order_sn'],'refund_time'=>$refundtime,'refund_money'=>$value['refund_money'],'refund_reason'=>$value['refund_reason'],'shopname'=>$value['shopname'],'platfromname'=>$value['platfromname'],'shopoper'=>$value['shopoper'],'goods_originask'=>$value['goods_originask'],'moneytype'=>$value['moneytype'],'ordertime'=>$value['ordertime']);

}

return json_encode($infoarr);

}


public function Cancel($pay_id){
//取消退款
$delsql="delete from db_paypal_refund where pay_id='{$pay_id}';";
$this->db->query($delsql);
//查看之前是否有退款如果没有的话底单状态改为正常订单，如果有的话，改为部分退款
$arr= $this->db->query('select * from db_paypal_refund where pay_id="'.$pay_id.'"')->result_array();
$str='正常订单';
if($arr[0]['order_sn']) $str="部分退款";
//db_order_list 表中退款状态改为0
$upsql1="UPDATE db_order_list SET  order_source='{$str}',is_backpay='0' where order_sn='{$order_sn}';";
$this->db->query($upsql1);

}


public function Agree($order_sn,$pay_id){
  $nowtime=time();
$upsql="UPDATE db_order_list SET is_backpay='2' where order_sn='{$order_sn}';";
$this->db->query($upsql);
$upsql="UPDATE db_paypal_refund SET refund_status='2',confirm_time='{$nowtime}' where pay_id='{$pay_id}';";
$this->db->query($upsql);
//如果为非magento和ebay的批准后通知财务
$sql="select * from `db_paypal_refund` where `pay_id`='{$pay_id}';";
$arr= $this->db->query($sql)->result_array();
if ($arr[0]['platfromname']!='magento' && $arr[0]['platfromname']!='eBay') {
  //查找需要发送消息的人
  $sql1="select * from `web_config` where `c_key`='paypalAudit';";
  $namestr=$this->db->query($sql1)->result_array();
  if(!isset($namestr[0]['c_value'])) return "已加入退款队列";
  $namearr=explode('|',$namestr[0]['c_value']);
  $this->load->library('Message');
  $randid = $this->message->getMessageNums();
    foreach ($namearr as $value) {
      $idsql="select `id` from `d_user` where `name`='{$value}';";
      $idarr=$this->db->query($idsql)->result_array();
      foreach ($idarr as $vid) {
       $postMessageData["message_type"] = $this->message->getMessageType(0);//消息提醒
       $message = "提交的退款申请已审批通过，订单编号为".$order_sn;
       $postMessageData["message"] = $message;
       $postMessageData["message_toName"] = $value;
       $postMessageData["message_toID"] = $vid['id'];
       $postMessageData["message_formID"] = 0;
       $postMessageData["isread"] =  $randid;
       $postMessageData["url"] = '/paypal/paypalRefundTo';
        $this->fun->saveMessage($postMessageData);
      } 
    }
  

}
//退款流程
return "已加入退款队列";
}



public function receipt($pay_id,$payreturnid){
       
       // $inssql="insert into `db_goods_log` set `goods_id`='{$imgdata["goods_id"]}',`updatetime`='{$imgdata["updatetime"]}',`createUid`='{$imgdata["createUid"]}',`types`='{$imgdata["types"]}',`content`='{$imgdata["content"]}',`createUser`='{$imgdata["createUser"]}';";
        //$this->db->query($inssql);
       // $this->db->query();

//return $this->db->insert_string('db_goods_log', $imgdata);
  //获得pay_id和回执内容
  //修改paypal退款状态，填写paypal内容，输出信息。
  $upsql="UPDATE `db_paypal_refund` SET `refund_status`='3',`payreturnid`='{$payreturnid}' where `pay_id`='{$pay_id}';";
  $this->db->query($upsql);


    $this->load->library('Message');
    $randid = $this->message->getMessageNums(); //获取当前存储的条数
    //确定接收消息的人员，提交人
    $sesql="select `order_sn`,`shopoper`,`order_id` from `db_paypal_refund` where `pay_id`='{$pay_id}';";
     $arr=$this->db->query($sesql)->result_array();
    foreach ($arr as $value) {
      $idsql="select `id` from `d_user` where `name`='{$value['shopoper']}';";
      $idarr=$this->db->query($idsql)->result_array();
      foreach ($idarr as $vid) {
       $postMessageData["message_type"] = $this->message->getMessageType(0);//消息提醒
       $message = "提交的退款申请已完成，订单编号为".$value['order_sn'];
       $message .=",paypal交易号为".$payreturnid;
       $postMessageData["message"] = $message;
       $postMessageData["message_toName"] = $value['shopoper'];
       $postMessageData["message_toID"] = $vid['id'];
       $postMessageData["message_formID"] = 0;
       $postMessageData["isread"] =  $randid;
       $postMessageData["url"] = '/paypal/paypalRefundTo';
        $this->fun->saveMessage($postMessageData);
      }
       $imgdata=array();
        $imgdata["goods_id"] = $value['order_id'];//订单ID
        $imgdata["updatetime"] = date("Y-m-d H:i:s");
        $imgdata["createUid"] = 0;
        $imgdata["types"] = 3;
        $imgdata["content"] ='退款成功,paypal退款流水号：'.$payreturnid;
        $imgdata["createUser"] ='auto';
        
         $this->db->query($this->db->insert_string('db_goods_log', $imgdata));
        
    }

        
}


//获取sql语句
public function displaytpl($where,$value,$TYPE,$starttime,$endtime,$STATUS){
    $sql="select * from `db_paypal_notes`";
    if(    $where != '*' 
      || $TYPE != '*' 
      || $starttime != '*'
      || $endtime != '*'
      || $STATUS != '*'){
    $sql.=" WHERE";
        if($where != '*') $sql .= " $where like '%{$value}%' AND";
        if($TYPE != '*'){
          $TYPE=str_replace('_',' ',$TYPE);
          if($TYPE=='Paymentp'){
            $TYPE='Payment';
            $sql.=" `AMT` >= 0 AND";
          }
          if($TYPE=='Paymentl'){
            $TYPE='Payment';
            $sql.=" `AMT` < 0 AND";
          }
          $sql .= " `TYPE` = '{$TYPE}' AND";
        }        
        if($starttime != '*') $sql .= " `TIMESTAMP` >= '{$starttime}' AND";
        if($endtime != '*') $sql .= " `TIMESTAMP` <= '{$endtime}' AND";
        if($STATUS != '*'){
          $STATUS=str_replace('_',' ',$STATUS);
          $sql .= " `STATUS`='{$STATUS}' AND";
        }     
        $sql .= " `TIMESTAMP` != ''";
    }
    
    return $sql;
}


//执行sql
public function getindextpl($sql,$page){
    $num = 100*($page - 1);
  $sql .= " ORDER BY `pn_id` DESC LIMIT $num,100;";
    return $this->db->query($sql)->result_array();
}

//获取总数
public function sqlcount($sql){
 $list=$this->db->query($sql)->result_array();
 return count($list);
}


}
?>